drop sequence seq_mylog;

create sequence seq_mylog
   increment by 1
   minvalue 1;

drop table DW_LOG;
drop type MyLog;

create type MyLog as object (
       ts timestamp,
       logsession number,
       loglevel number,
       message varchar2(2000),

       member procedure error (msg in varchar2),
       member procedure warn  (msg in varchar2),
       member procedure info  (msg in varchar2),
       member procedure dbg (msg in varchar2),
       member function LOG_DEBUG return number,
       member function LOG_INFO return number,
       member function LOG_WARN return number,
       member function LOG_ERROR return number,
       member procedure newsession,
       member function logprepare(level in number, msg in varchar2) return MyLog,
       member procedure write (level in number, msg in varchar2)
);
/
show err;

create table DW_LOG of MyLog;



       create type body MyLog as
          member function LOG_DEBUG return number is begin return 0; end;
          member function LOG_INFO  return number is begin return 1; end;
          member function LOG_WARN  return number is begin return 2; end;
          member function LOG_ERROR return number is begin return 4; end;

           member procedure error (msg in varchar2) is
               begin
                  write(LOG_ERROR, msg);
               end error;

           member procedure warn  (msg in varchar2) is
               begin
                  write(LOG_WARN, msg);
               end warn;

           member procedure info  (msg in varchar2) is
               begin
                  write(LOG_INFO, msg);
               end info;

           member procedure dbg  (msg in varchar2) is
               begin
                  write(LOG_DEBUG, msg);
               end dbg;

           member procedure newsession is
               curlog number;
               begin
                  select seq_mylog.nextval into curlog from dual;
               end newsession;
  
           member function logprepare(level in number, msg in varchar2) return MyLog is
               row_log MyLog; 
               begin
                  select seq_mylog.currval into row_log.logsession from dual;
                  row_log.ts :=  CURRENT_TIMESTAMP; --provare a non inizializzarlo per usare valore default
                  row_log.loglevel := level;
                  row_log.message := msg;
                
                  return row_log;
               end logprepare;


           member procedure write (level in number, msg in varchar2) is
           --rowlog  dw_log%rowtype;
           begin
              --rowlog := logprepare(level, msg);
              insert into dw_log values(logprepare(level, msg)); --values rowlog;
           end write;

    end;
/
show err;
